package edu.gatech.cc.CFAWaffleFriesWheresMyStuff.common;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Operates a SQLite database stored on the device.
 * Interfaces with the application through the Datastore interface definition
 */
public class WMSDatabase extends SQLiteOpenHelper implements Datastore
{
	private static String WMS_DB_PATH = "/data/data/edu.gatech.cc.CFAWaffleFriesWheresMyStuff/databases/";
	private static String WMS_DB_NAME = "WMSdatabase";
	private static String USER_TABLE = "UserTable";
	private static String ITEM_TABLE = "ItemTable";
	private final Context dbContext;
	private SQLiteDatabase WMSTable = null;
	
	/**
	 * Constructs an instance of a WMSDatabase opened with the given context.
	 * @param context
	 */
	public WMSDatabase(Context context)
	{
		super(context, WMS_DB_NAME, null, 1);
		this.dbContext = context;
	}

	/**
	 * Sets up the database on the Android device.
	 * If the database does not exist yet, it is created and the default data is copied to it.
	 * @throws IOException Thrown if there is a problem opening the database file.
	 * @throws DatabaseCopyException Thrown if there is a problem copying the default data.
	 */
	public void createDatabase() throws IOException, DatabaseCopyException
	{
		boolean alreadyExists = checkDatabase();
		if(alreadyExists == false)
		{
			this.getReadableDatabase();
			try
			{
				copyDatabase();
			}
			catch(IOException ioe)
			{
				throw new DatabaseCopyException();
			}
		}
	}
	
	/**
	 * Checks if the database file exists already
	 * @return True if the file exists, false otherwise.
	 */
	private boolean checkDatabase()
	{
		SQLiteDatabase dbCheck = null;
		try
		{
			String filePath = WMS_DB_PATH + WMS_DB_NAME;
			dbCheck = SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READONLY);
		}
		catch(SQLiteException sqle){}
		if(dbCheck != null)
			dbCheck.close();
		return dbCheck != null ? true : false;
	}
	
	/**
	 * Copies the default data to the database file.
	 * @throws IOException Thrown if there is an error while copying the data or opening the file.
	 */
	private void copyDatabase() throws IOException
	{
		InputStream input = dbContext.getAssets().open(WMS_DB_NAME+".db");
		String outputFilepath = WMS_DB_PATH + WMS_DB_NAME;
		OutputStream output = new FileOutputStream(outputFilepath);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = input.read(buffer)) > 0)
			output.write(buffer, 0, length);
		output.flush();
		output.close();
		input.close();
	}

	/**
	 * Opens the database for reading and writing.
	 * @throws SQLException If SQLite experiences an error opening the database.
	 */
	public void openDatabase() throws SQLException
    {
    	String filepath = WMS_DB_PATH + WMS_DB_NAME;
    	WMSTable = SQLiteDatabase.openDatabase(filepath, null, SQLiteDatabase.OPEN_READWRITE);
    }
	
	public void saveUser(User user)
	{
		if(userExists(user.getUsername()))
		{
			Cursor dbCursor = WMSTable.query(USER_TABLE, null, null, null, null, null, null);
			String[] columnNames = dbCursor.getColumnNames();
			ContentValues cv = new ContentValues();
			
			Cursor idCursor = WMSTable.rawQuery("SELECT * FROM UserTable WHERE Username = '" + user.getUsername() + "'", null);
			idCursor.moveToFirst();
			cv.put(columnNames[0], idCursor.getInt(0));
			cv.put(columnNames[1], user.getUsername());
			cv.put(columnNames[2], user.getPassword());
			cv.put(columnNames[3], user.getName());
			cv.put(columnNames[4], user.getPhone());
			cv.put(columnNames[5], user.getEmail());
			cv.put(columnNames[6], user.getAttempts());
			cv.put(columnNames[7], user.getLockStatus() ? 1 : 0);
			cv.put(columnNames[8], user.isAdmin()? 1 : 0);
			
			WMSTable.replace(USER_TABLE, null, cv);
		} else {
	    	Cursor dbCursor = WMSTable.query(USER_TABLE, null, null, null, null, null, null);
			String[] columnNames = dbCursor.getColumnNames();
			ContentValues cv = new ContentValues();
			
			cv.putNull(columnNames[0]);
			cv.put(columnNames[1], user.getUsername());
			cv.put(columnNames[2], user.getPassword());
			cv.put(columnNames[3], user.getName());
			cv.put(columnNames[4], user.getPhone());
			cv.put(columnNames[5], user.getEmail());
			cv.put(columnNames[6], 0);
			cv.put(columnNames[7], 0);
			cv.put(columnNames[8], 0);
			
			WMSTable.insert(USER_TABLE, null, cv);
		}	
	}
	
    public void saveAdmin(Admin admin)
    {
    	if(userExists(admin.getUsername()))
    	{
    		Cursor dbCursor = WMSTable.query(USER_TABLE, null, null, null, null, null, null);
			String[] columnNames = dbCursor.getColumnNames();
			ContentValues cv = new ContentValues();
			
			Cursor idCursor = WMSTable.rawQuery("SELECT * FROM UserTable WHERE Username = '" + admin.getUsername() + "'", null);
			idCursor.moveToFirst();
			cv.put(columnNames[0], idCursor.getInt(0));
			cv.put(columnNames[1], admin.getUsername());
			cv.put(columnNames[2], admin.getPassword());
			cv.put(columnNames[3], admin.getName());
			cv.put(columnNames[4], admin.getPhone());
			cv.put(columnNames[5], admin.getEmail());
			cv.put(columnNames[6], admin.getAttempts());
			cv.put(columnNames[7], admin.getLockStatus() ? 1 : 0);
			cv.put(columnNames[8], admin.isAdmin() ? 1 : 0);
			
			WMSTable.replace(USER_TABLE, null, cv);
    	} else {
	    	Cursor dbCursor = WMSTable.query(USER_TABLE, null, null, null, null, null, null);
			String[] columnNames = dbCursor.getColumnNames();
			ContentValues cv = new ContentValues();
			
			cv.putNull(columnNames[0]);
			cv.put(columnNames[1], admin.getUsername());
			cv.put(columnNames[2], admin.getPassword());
			cv.put(columnNames[3], admin.getName());
			cv.put(columnNames[4], admin.getPhone());
			cv.put(columnNames[5], admin.getEmail());
			cv.put(columnNames[6], 0);
			cv.put(columnNames[7], 0);
			cv.put(columnNames[8], 1);
			
			WMSTable.insert(USER_TABLE, null, cv);
    	}	
    }
	
    public void deleteUser(String username, String adminUsername)
    		throws UserDoesNotExistException, UserIsNotAdminException, CanNotDeleteSelfException
    {
    	if(userExists(username) == false)
    		throw new UserDoesNotExistException();
    	else if(checkAdmin(adminUsername) == false)
    			throw new UserIsNotAdminException();
    	else if(username.equals(adminUsername))
    		throw new CanNotDeleteSelfException();
    	else
    	{
    		String condition = "Username = '" + username + "'";
    		WMSTable.delete(USER_TABLE, condition, null);
    	}
    }
    
    public boolean userExists(String username)
    {
    	String queryString = "SELECT Username FROM UserTable WHERE Username = '" + username + "'";
    	Cursor dbCursor = WMSTable.rawQuery(queryString, null);
    	return dbCursor.getCount() > 0;
    }
    
    public boolean checkAdmin(String adminUsername)
    {
    	String queryString = "Select * FROM UserTable WHERE Username = '" + adminUsername + "'";
    	Cursor dbCursor = WMSTable.rawQuery(queryString, null);
		dbCursor.moveToFirst();
		int isAdmin = dbCursor.getInt(8);
		if(isAdmin == 1)
			return true;
		else
			return false;
    }
    
    public User getUser(String user_name) throws UserDoesNotExistException
    {
    	if(userExists(user_name) == false)
    		throw new UserDoesNotExistException();
    	else
    	{
    		String queryString = "SELECT * FROM UserTable WHERE Username = '" + user_name + "'";
			Cursor dbCursor = WMSTable.rawQuery(queryString, null);
			dbCursor.moveToFirst();
			String username = dbCursor.getString(1);
			String password = dbCursor.getString(2);
			String name = dbCursor.getString(3);
			String phoneNumber = dbCursor.getString(4);
			String email = dbCursor.getString(5);
			int loginAttempts = dbCursor.getInt(6);
			boolean isLocked = dbCursor.getInt(7) == 1 ? true : false;
			boolean adminCheck = dbCursor.getInt(8) == 1 ? true : false;
			if(adminCheck)
				return new Admin(username, password, name, phoneNumber, email, loginAttempts, isLocked);
			else
				return new User(username, password, name, phoneNumber, email, loginAttempts, isLocked);
    	}
    }
    
    /**
     * Returns the ID for the User with the given username.
     * @param username The username to search for
     * @return The ID for the given User
     * @throws UserDoesNotExistException Thrown if the given username cannot be found in the database.
     */
    protected int getUserId(String username) throws UserDoesNotExistException
    {
    	if(userExists(username) == false)
    		throw new UserDoesNotExistException();
    	else
    	{
    		String queryString = "SELECT * FROM UserTable WHERE Username = '" + username + "'";
			Cursor dbCursor = WMSTable.rawQuery(queryString, null);
			dbCursor.moveToFirst();
			return dbCursor.getInt(0);
    	}
    }
    
    public List<String> getAllUsernames()
    {
    	ArrayList<String> usernameList = new ArrayList<String>();
    	String queryString = "SELECT Username FROM UserTable";
    	Cursor dbCursor = WMSTable.rawQuery(queryString, null);
    	if(dbCursor.moveToFirst())
    	{
    		while(!dbCursor.isAfterLast())
    		{
    			String username = dbCursor.getString(dbCursor.getColumnIndex("Username"));
    			usernameList.add(username);
    			dbCursor.moveToNext();
    		}
    	}
    	return usernameList;
    }
    
    public void unlock(String username) throws UserDoesNotExistException
	{
    	if(userExists(username) == false)
    		throw new UserDoesNotExistException();
    	else
    	{
    		String queryString = "UPDATE UserTable SET IsLocked = 0 WHERE Username = '" + username + "'";
    		WMSTable.execSQL(queryString);
    	}
	}
    
    public void turnUserToAdmin(String username) throws UserDoesNotExistException
    {
    	if(userExists(username) == false)
    		throw new UserDoesNotExistException();
    	else
    	{
    		String queryString = "UPDATE UserTable SET IsAdmin = 1 WHERE Username = '" + username + "'";
    		WMSTable.execSQL(queryString);
    	}
    }
    
    public void saveItem(Item item)
    {
    	boolean itemExists = ( item.getID() != null ) ? itemExists(item.getID()) : false;
    	Cursor dbCursor = WMSTable.query(ITEM_TABLE, null, null, null, null, null, null);
		String[] columnNames = dbCursor.getColumnNames();
		ContentValues cv = new ContentValues();
		
		if(itemExists)
		{
			cv.put(columnNames[0], item.getID());
			try {
				cv.put(columnNames[1], getUserId(item.getCreatorName()));
			} catch (UserDoesNotExistException e) {
				e.printStackTrace();
			}
			cv.put(columnNames[2], item.getName());
			cv.put(columnNames[3], item.getDescription());
			cv.put(columnNames[4], item.isOpen());
			cv.put(columnNames[5], item.getCategory().toString());
			cv.put(columnNames[6], item.getType().toString());
			cv.put(columnNames[7], item.getLocation());
			cv.put(columnNames[8], item.date.getTime()/1000);
			cv.put(columnNames[9], item.getReward());
			
			WMSTable.replace(ITEM_TABLE, null, cv);
		} else {
			cv.putNull(columnNames[0]);
			try {
				cv.put(columnNames[1], getUserId(item.getCreatorName()));
			} catch (UserDoesNotExistException e) {
				e.printStackTrace();
			}
			cv.put(columnNames[2], item.getName());
			cv.put(columnNames[3], item.getDescription());
			cv.put(columnNames[4], item.isOpen());
			cv.put(columnNames[5], item.getCategory().toString());
			cv.put(columnNames[6], item.getType().toString());
			cv.put(columnNames[7], item.getLocation());
			cv.put(columnNames[8], item.date.getTime()/1000);
			cv.put(columnNames[9], item.getReward());
			
			WMSTable.insert(ITEM_TABLE, null, cv);
		}
    }
    
    public Item getItem(int ID) throws ItemDoesNotExistException
    {
    	Cursor dbCursor = WMSTable.rawQuery("SELECT * FROM ItemTable WHERE _id = " + ID, null);
    	
    	if(dbCursor.getCount() == 0)
    	{
    		throw new ItemDoesNotExistException();
    	}
    	
    	return null;
    }
    
    
    public List<Item> getUserItems(String user_name)
    {
    	ArrayList<Item> itemList = new ArrayList<Item>();
    	if(userExists(user_name)){
	    	String idQueryString = "SELECT _id FROM UserTable WHERE Username = '" + user_name + "'";
	    	Cursor dbCursor1 = WMSTable.rawQuery(idQueryString, null);
	    	dbCursor1.moveToFirst();
	    	int userID = dbCursor1.getInt(0);
	    	
	    	
	    	String itemQueryString = "SELECT * FROM ItemTable WHERE UserID = '" + userID + "'";
	    	Cursor dbCursor2 = WMSTable.rawQuery(itemQueryString, null);
	    	if(dbCursor2.moveToFirst())
	    	{
	    		while(!dbCursor2.isAfterLast())
	    		{
	    			int id = dbCursor2.getInt(0);
	    			String name = dbCursor2.getString(2);
	    			String description = dbCursor2.getString(3);
	    			int isOpen = dbCursor2.getInt(4);
	    			boolean open = true;
	    			if(isOpen == 0)
	    				open = false;
	    			String category = dbCursor2.getString(5);
	    			String type = dbCursor2.getString(6);
	    			String location = dbCursor2.getString(7);
	    			long date = dbCursor2.getLong(8);
	    			double reward = dbCursor2.getDouble(9);
	    			
	    			itemList.add(new Item(id, user_name, name, description, open, Item.Category.parse(category), Item.Type.parse(type), location, date, reward));
	    			dbCursor2.moveToNext();
	    		}
	    	}
    	}
    	return itemList;
    }
    
    public List<Item> filterItems(String type, Boolean openStatus, String category, String name, Long date, String location, String user_name)
    {
    	int isOpen = 0;	
    	ArrayList<Item> itemList = new ArrayList<Item>();
    	String queryString = "SELECT * FROM ItemTable";
    	if(type != null || openStatus != null || category != null || name != null || date != null || location != null || user_name != null)
    		queryString = queryString + " WHERE ";
    	
    	if(type != null)
    		queryString = queryString + "Type = '" + type + "'";
    	if(openStatus != null)
    	{
    		if(openStatus.booleanValue() == true)
        		isOpen = 1;
    		if(type != null)
    			queryString = queryString + " AND ";
    		queryString = queryString + "IsOpen = '" + isOpen + "'";
    	}
    	if(category != null)
    	{
    		if(type != null || openStatus != null)
    			queryString = queryString + " AND ";
    		queryString = queryString + "Category = '" + category + "'";
    	}
    	if(name != null)
    	{
    		if(type != null || openStatus != null || category != null)
    			queryString = queryString + " AND ";
    		queryString = queryString + "Name = '" + name + "'";
    	}
    	if(date != null)
    	{
    		long longDate = date.longValue();
    		if(type != null || openStatus != null || category != null || name != null)
    			queryString = queryString + " AND ";
    		queryString = queryString + "Date >= '" + longDate + "'";
    	}
    	if(location != null)
    	{
    		if(type != null || openStatus != null || category != null || name != null || date != null)
    			queryString = queryString + " AND ";
    		queryString = queryString + "Location = '" + location + "'";
    	}
    	if(user_name != null)
    	{
    		if(type != null || openStatus != null || category != null || name != null || date != null || location != null)
    			queryString = queryString + " AND ";
    		try
    		{
    			int user_id = getUserId(user_name);
    			queryString = queryString + "UserID = '" + user_id + "'";
    		}
    		catch(UserDoesNotExistException e)
    		{
    			return new ArrayList<Item>();
    		}
    	}

    	
    	Cursor dbCursor = WMSTable.rawQuery(queryString, null);
    	if(dbCursor.moveToFirst())
    	{
    		while(!dbCursor.isAfterLast())
    		{
    			int itemID = dbCursor.getInt(0);
    			int userID = dbCursor.getInt(1);
    			String username = getUsername(userID);
    			String itemName = dbCursor.getString(2);
    			String itemDesc = dbCursor.getString(3);
    			int openBoolean = dbCursor.getInt(4);
    			boolean open = true;
    			if(openBoolean == 0)
    				open = false;
    			String itemCat = dbCursor.getString(5);
    			String itemType = dbCursor.getString(6);
    			String itemLoc = dbCursor.getString(7);
    			long itemDate = dbCursor.getLong(8);
    			double reward = dbCursor.getDouble(9);
    			
    			itemList.add(new Item(itemID, username, itemName, itemDesc, open, Item.Category.parse(itemCat), Item.Type.parse(itemType), itemLoc, itemDate, reward));
    			dbCursor.moveToNext();
    		}
    	}
    	return itemList;
    }
    
    /**
     * Gets the username for the User with the given ID.
     * @param userID The user ID to search for
     * @return The username of the given user
     */
    private String getUsername(int userID)
    {
    	String queryString = "SELECT * FROM UserTable WHERE _id = '" + userID + "'";
		Cursor dbCursor = WMSTable.rawQuery(queryString, null);
		dbCursor.moveToFirst();
		return dbCursor.getString(1);
    }

    public Integer getNextItemID()
    {
    	Cursor dbCursor = WMSTable.rawQuery("SELECT * FROM ItemTable", null);
    	if(dbCursor.getCount() == 0)
    	{
    		return 0;
    	}
    	if(dbCursor.moveToLast())
    	{
    		return dbCursor.getInt(0)+1;
    	} else {
    		return null;
    	}
    }
    
    @Override
    public void onCreate(SQLiteDatabase database){}
    
    @Override
    public void onUpgrade(SQLiteDatabase database, int a, int b){}
    
    /**
     * Class for packaging exceptions encountered when copying the default data to the database.
     *
     */
    public class DatabaseCopyException extends Exception
	{
		/**
		 * Auto-generated default serialVersionUID
		 */
		private static final long serialVersionUID = 1L;	
	}

	@Override
	public boolean itemExists(int ID) {
		Cursor dbCursor = WMSTable.rawQuery("SELECT * FROM ItemTable WHERE _id = " + ID, null);
		return dbCursor.getCount() > 0;
	}
    
}
